﻿BEGIN TRY
	BEGIN TRAN

	MERGE [dbo].[TransformationBreakdown] AS TGT 
	USING
	(
		-- SCD2 From
		SELECT 211, 21, 'Value when inserting first record for the business key', 1, '''1900-01-01'',getutcdate(),getdate()', '''1900-01-01''' UNION ALL
		SELECT 212, 21, 'Value when inserting next record for the business key', 1, 'getutcdate(),getdate(),''1900-01-01''', 'getutcdate()' UNION ALL
		-- SCD2 To
		SELECT 221, 22, 'Value when inserting new record for the business key', 1, '''9999-12-31'',''2999-12-31''', '''9999-12-31''' UNION ALL
		SELECT 222, 22, 'Value when expiring record for the business key', 1, 'getdate(),getutcdate()', 'getutcdate()' UNION ALL
		SELECT 223, 22, 'Value when logically deleting record for the business key', 1, 'getdate(),getutcdate()', 'getutcdate()' UNION ALL
		-- SCD2 Is Active
		SELECT 231, 23, 'Value when record should be marked as active', 1, '1,''Y'',''YES''', '''Y''' UNION ALL
		SELECT 232, 23, 'Value when record should be marked as inactive', 1, '0,''N'',''NO''', '''N''' UNION ALL
		-- SCD3 Previous
		SELECT 321, 32,	'Value when inserting first record for the business key', 1, 'NULL', 'NULL' UNION ALL
		-- SCD3 From
		SELECT 331, 33, 'Value when inserting or updating SCD3 attribute', 1, 'getdate(),getutcdate()', 'getutcdate()' UNION ALL
		-- Created Timestamp
		SELECT 801, 80, 'Value when creating first instance of the record', 1, 'getdate(),getutcdate()', 'getutcdate()' UNION ALL
		-- Modified Timestamp
		SELECT 811, 81, 'Value when updating existing record', 1, 'getdate(),getutcdate()', 'getutcdate()' UNION ALL
		-- Deleted Timestamp
		SELECT 821, 81, 'Value when logically deleting existing record', 1, 'getdate(),getutcdate()', 'getutcdate()' UNION ALL
		-- Is Deleted
		SELECT 841, 84, 'Value when record should be flagged as deleted', 1, '1,''Y'',''DEL'',''Kaput''', 'Y' UNION ALL
		SELECT 842, 84, 'Value when record should be flagged as not deleted', 1, '0,''N'',''NOT DEL'',''Still here''', 'N' UNION ALL
		-- Version
		SELECT 851, 85, 'Value when inserting first record for the business key', 1, '1,10,87', '1' UNION ALL
		SELECT 852, 85, 'Increment value', 1, '10,20,55', '1' UNION ALL
		-- Hash
		SELECT 861, 86, 'Hash algorithm used when inserting or updating regular transformation attribute', 1, '''MD2'',''MD4'',''MD5'',''SHA'',''SHA1'',''SHA_256'',''SHA_512''', '''SHA_512'''
	) AS SRC ([TransformationBreakdownId], [TransformationId], [Breakdown], [AllowsFreeText], [Values], [DefaultValue])
	ON TGT.[TransformationBreakdownId] = SRC.[TransformationBreakdownId]  

	WHEN MATCHED THEN   
	UPDATE SET
		[TransformationId] = SRC.[TransformationId],
		[Breakdown] = SRC.[Breakdown],
		[AllowsFreeText] = SRC.[AllowsFreeText],
		[Values] = SRC.[Values],
		[DefaultValue] = SRC.[DefaultValue]
	WHEN NOT MATCHED BY TARGET THEN  
		INSERT ([TransformationBreakdownId], [TransformationId], [Breakdown], [AllowsFreeText], [Values], [DefaultValue])
		VALUES (SRC.[TransformationBreakdownId], SRC.[TransformationId], SRC.[Breakdown], SRC.[AllowsFreeText], SRC.[Values], SRC.[DefaultValue])

	WHEN NOT MATCHED BY SOURCE THEN  
		DELETE;

	COMMIT TRAN;
END TRY
BEGIN CATCH
	ROLLBACK TRAN;
END CATCH
